﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using PiscoSourDominio;
using PiscoSourSOAPRestServices.Persistencia;
using Dapper;

namespace PiscoSourRESTServices.Persistencia
{
    public class ReservaDAO
    {

        public Reserva ObtenerReserva(string codigo)
        {
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                const string sqlQuery = "select * from psr_reserva where intreserva=@intreserva";
                var reserva = sqlConnection.Query<Reserva>(sqlQuery, new { intreserva = codigo }).SingleOrDefault<Reserva>();

                sqlConnection.Close();

                return reserva;
            }
        }

        public IEnumerable<Reserva> ListarReservas()
        {
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                var sqlQuery = string.Format(@"Select * from psr_reserva");
                var result = sqlConnection.Query<Reserva>(sqlQuery);
                sqlConnection.Close();
                return result;
            }
        }

        public IEnumerable<Reserva> ListarReservasDelDiaCambio()
        {

            /*const string sqlQuery = "begin  set dateformat ymd;"
                        + "select intReserva as 'ID',dtFecReserva as 'Fecha Reserva',"
                        + "PRS_LOCAL.vchnombre as 'Local',"
                        + "IntCantidadPersonas as 'Comenzales',"
                        + "prs_cliente.vchNombre + ' ' + prs_cliente.vchApellido as Cliente,"
                        + "psr_reserva.vchUsuario as 'Usuario' "
                        + "from psr_reserva,PRS_LOCAL,prs_cliente "
                        + "where psr_reserva.intcodlocal=PRS_LOCAL.intCodlocal "
                        + "and psr_reserva.intCliente=prs_cliente.IntCliente "
                        + "and dtFecReserva>@minutos; end";*/

            const string sqlQuery = "begin set dateformat ymd;"
                        + "select * "
                        + "from PSR_reserva "
                        + "where dtFecReserva>@minutos; end";
            
            String strfechahoy = DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss");
            
            using (var sqlConnection = ConnectionFactory.GetConnection())
            {
                sqlConnection.Open();
                var result = sqlConnection.Query<Reserva>(sqlQuery, new { minutos = strfechahoy });

                sqlConnection.Close();

                return result;
            }
    }

        public List<Reserva> ListarReservasDelDia()
        {

            /*string sql = "begin  set dateformat ymd;"
                        + "select intReserva as 'ID',dtFecReserva as 'Fecha Reserva',"
                        + "PRS_LOCAL.vchnombre as 'Local',"
                        + "IntCantidadPersonas as 'Comenzales',"
                        + "prs_cliente.vchNombre + ' ' + prs_cliente.vchApellido as Cliente,"
                        + "psr_reserva.vchUsuario as 'Usuario' "
                        + "from psr_reserva,PRS_LOCAL,prs_cliente "
                        + "where psr_reserva.intcodlocal=PRS_LOCAL.intCodlocal "
                        + "and psr_reserva.intCliente=prs_cliente.IntCliente "
                        + "and dtFecReserva>'" + DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss") + "'; end";*/

            string sql = "begin  set dateformat ymd;"
                        + "select intReserva,dtFecReserva,vchUsuario,intCodLocal,blnEstado,intCantidadPersonas,intCliente "
                        + "from psr_reserva "
                        + "where dtFecReserva>'" + DateTime.Now.AddMinutes(30).ToString("yyyy-MM-dd HH:mm:ss") + "'; end";
            
            Reserva OReserva;
            List<Reserva> lista = new List<Reserva>();


            ClienteDAO clienteDAO = new ClienteDAO();
            LocalDAO localDAO = new LocalDAO();

            using (SqlConnection con = new SqlConnection(ConnectionFactory.Connectionstring))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;

                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {

                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1);
                        OReserva.vchUsuario = rd.GetString(2);
                        OReserva.intCodLocal = rd.GetInt32(3);
                        OReserva.blnEstado = rd.GetString(4);
                        OReserva.intCantidadPersonas = rd.GetInt32(5);
                        OReserva.intCliente = rd.GetInt32(6);

                        OReserva.cliente = clienteDAO.ObtenerClientes(OReserva.intCliente);
                        OReserva.local = localDAO.ObtenerLocal(OReserva.intCodLocal);
                        
                        /*OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1).ToString("dd/MM/yyyy HH:mm");
                        OReserva.StrLocal = rd.GetString(2);
                        OReserva.intCantidadPersonas = rd.GetInt32(3);
                        OReserva.chrCliente = rd.GetString(4);
                        OReserva.vchUsuario = rd.GetString(5);*/
                        lista.Add(OReserva);
                    }
                }
            }


            return lista;
        }

    #region Antigua
        /*public Reserva ObtenerReservas(string codigo)
        {
            string sql = "select * from psr_reserva where intreserva='" + codigo + "'";

            Reserva OReserva=null;
            using (SqlConnection con = new SqlConnection(ConexionUtil.Cadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;

                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {
                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1).ToString("dd/MM/yyyy HH:mm");
                        OReserva.vchUsuario = rd.GetString(2);
                        OReserva.intCodLocal = rd.GetInt32(3);
                        OReserva.blnEstado = rd.GetString(4);
                        OReserva.intCantidadPersonas = rd.GetInt32(5);
                        OReserva.vchUsuario = rd.GetString(6);
                    }
                }
            }

            return OReserva;
        }

        public List<Reserva> ListarReservas()
        {

            string sql = "select * from psr_reserva";

            Reserva OReserva;
            List<Reserva> lista = new List<Reserva>();
            using (SqlConnection con = new SqlConnection(ConexionUtil.Cadena))
            {
                con.Open();
                using (SqlCommand com = new SqlCommand(sql, con))
                {
                    SqlDataReader rd;
            
                    rd = com.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                    while (rd.Read())
                    {
                        OReserva = new Reserva();
                        OReserva.IntReserva = rd.GetInt32(0);
                        OReserva.dtFecReserva = rd.GetDateTime(1).ToString("dd/MM/yyyy HH:mm");
                        OReserva.vchUsuario = rd.GetString(2);
                        OReserva.intCodLocal = rd.GetInt32(3);
                        OReserva.blnEstado = rd.GetString(4);
                        OReserva.intCantidadPersonas = rd.GetInt32(5);
                        OReserva.vchUsuario = rd.GetString(6);

                        lista.Add(OReserva);
                    }
                }
            }


            return lista;
        }*/
    #endregion
    }
}